from flask import Flask, render_template, request, redirect, url_for, flash, session
from db import get_db_connection
import os



app = Flask(__name__)
app.secret_key = os.urandom(24)#设置会话密钥

# 在Flask应用启动前确保static/images目录存在
if not os.path.exists('static'):
    os.mkdir('static')
if not os.path.exists('static/images'):
    os.mkdir('static/images')

# 确保templates/admin目录存在
if not os.path.exists('templates'):
    os.mkdir('templates')
if not os.path.exists('templates/admin'):
    os.mkdir('templates/admin')

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/admin')
def admin_index():
    return render_template('admin/index.html')

# 管理员-客户信息管理
@app.route('/admin/customers', methods=['GET', 'POST'])
def admin_customers():
    conn = get_db_connection()  #建立数据库连接
    query = "SELECT * FROM userinfo WHERE 1=1"
    params = []  #参数列表
    # 处理模糊查询
    name = request.args.get('name', '').strip()  #获取值并去除空白字符
    pid = request.args.get('pid', '').strip()
    telephone = request.args.get('telephone', '').strip()
    if name:
        query += " AND customerName LIKE %s"
        params.append(f"%{name}%")
    if pid:
        query += " AND PID LIKE %s"
        params.append(f"%{pid}%")
    if telephone:
        query += " AND telephone LIKE %s"
        params.append(f"%{telephone}%")
    with conn.cursor() as cursor:  #建立游标对象
        cursor.execute(query, params)
        customers = cursor.fetchall()  #返回查询的所有行
    conn.close()  #关闭连接
    return render_template('admin/customers.html', customers=customers, name=name, pid=pid, telephone=telephone)

@app.route('/admin/customers/add', methods=['GET', 'POST'])
def admin_customer_add():
    if request.method == 'POST':
        customerName = request.form['customerName']
        PID = request.form['PID']
        telephone = request.form['telephone']
        address = request.form['address']
        # 验证身份证号长度（中国身份证18位）
        if len(PID) != 18:
            flash('身份证号必须是18位')
            return redirect(url_for('admin_customer_add'))

        # 验证电话号码长度（假设11位手机号）
        if len(telephone) != 11:
            flash('电话号码必须是11位')
            return redirect(url_for('admin_customer_add'))
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute("INSERT INTO userinfo (customerName, PID, telephone, address) VALUES (%s, %s, %s, %s)",
                           (customerName, PID, telephone, address))
            conn.commit()  #提交事务
        conn.close()
        flash('客户添加成功！')
        return redirect(url_for('admin_customers'))
    return render_template('admin/customer_add.html')

@app.route('/admin/customers/edit/<int:customerID>', methods=['GET', 'POST'])
def admin_customer_edit(customerID):
    conn = get_db_connection()
    if request.method == 'POST':
        customerName = request.form['customerName']
        PID = request.form['PID']
        telephone = request.form['telephone']
        address = request.form['address']
        with conn.cursor() as cursor:
            cursor.execute("UPDATE userinfo SET customerName=%s, PID=%s, telephone=%s, address=%s WHERE customerID=%s",
                           (customerName, PID, telephone, address, customerID))
            conn.commit()
        conn.close()
        flash('客户信息修改成功！')
        return redirect(url_for('admin_customers'))
    else:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM userinfo WHERE customerID=%s", (customerID,))
            customer = cursor.fetchone()  #返回查询结果
        conn.close()
        if not customer:
            flash('未找到该客户！')
            return redirect(url_for('admin_customers'))
        return render_template('admin/customer_edit.html', customer=customer)

@app.route('/admin/customers/delete/<int:customerID>', methods=['POST'])
def admin_customer_delete(customerID):
    conn = get_db_connection()
    with conn.cursor() as cursor:
        cursor.execute("DELETE FROM userinfo WHERE customerID=%s", (customerID,))
        conn.commit()
    conn.close()
    flash('客户删除成功！')
    return redirect(url_for('admin_customers'))

# 银行卡管理
@app.route('/admin/cards', methods=['GET', 'POST'])
def admin_cards():
    conn = get_db_connection()
    query = "SELECT * FROM cardinfo WHERE 1=1"
    params = []
    cardID = request.args.get('cardID', '').strip()
    customerID = request.args.get('customerID', '').strip()
    isReportLoss = request.args.get('isReportLoss', '').strip()
    if cardID:
        query += " AND cardID LIKE %s"
        params.append(f"%{cardID}%")
    if customerID:
        query += " AND customerID LIKE %s"
        params.append(f"%{customerID}%")
    if isReportLoss:
        query += " AND IsReportLoss LIKE %s"
        params.append(f"%{isReportLoss}%")
    with conn.cursor() as cursor:
        cursor.execute(query, params)
        cards = cursor.fetchall()
    conn.close()
    return render_template('admin/cards.html', cards=cards, cardID=cardID, customerID=customerID, isReportLoss=isReportLoss)

@app.route('/admin/cards/add', methods=['GET', 'POST'])
def admin_card_add():
    if request.method == 'POST':
        cardID = request.form['cardID']
        curID = request.form['curID']
        savingID = request.form['savingID']
        openDate = request.form['openDate']
        openMoney = request.form['openMoney']
        balance = request.form['balance']
        passwd = request.form['pass']
        IsReportLoss = request.form['IsReportLoss']
        customerID = request.form['customerID']
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute("INSERT INTO cardinfo (cardID, curID, savingID, openDate, openMoney, balance, pass, IsReportLoss, customerID) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
                           (cardID, curID, savingID, openDate, openMoney, balance, passwd, IsReportLoss, customerID))
            conn.commit()
        conn.close()
        flash('银行卡添加成功！')
        return redirect(url_for('admin_cards'))
    return render_template('admin/card_add.html')


@app.route('/admin/cards/edit/<cardID>', methods=['GET', 'POST'])
def admin_card_edit(cardID):
    conn = get_db_connection()
    if request.method == 'POST':
        curID = request.form['curID']
        savingID = request.form['savingID']
        openDate = request.form['openDate']
        openMoney = request.form['openMoney']
        balance = request.form['balance']
        passwd = request.form['pass']
        IsReportLoss = request.form['IsReportLoss']
        customerID = request.form['customerID']
        with conn.cursor() as cursor:
            cursor.execute(
                "UPDATE cardinfo SET curID=%s, savingID=%s, openDate=%s, openMoney=%s, balance=%s, pass=%s, IsReportLoss=%s, customerID=%s WHERE cardID=%s",
                (curID, savingID, openDate, openMoney, balance, passwd, IsReportLoss, customerID, cardID))
            conn.commit()
        conn.close()
        flash('银行卡信息修改成功！')
        return redirect(url_for('admin_cards'))
    else:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM cardinfo WHERE cardID=%s", (cardID,))
            card = cursor.fetchone()
        conn.close()
        if not card:
            flash('未找到该银行卡！')
            return redirect(url_for('admin_cards'))

        # 将日期格式化为HTML date input需要的格式 (YYYY-MM-DD)
        if card['openDate']:
            formatted_date = card['openDate'].strftime('%Y-%m-%d')
        else:
            formatted_date = ''

        return render_template('admin/card_edit.html', card=card, formatted_date=formatted_date)

@app.route('/admin/cards/delete/<cardID>', methods=['POST'])
def admin_card_delete(cardID):
    conn = get_db_connection()
    with conn.cursor() as cursor:
        cursor.execute("DELETE FROM cardinfo WHERE cardID=%s", (cardID,))
        conn.commit()
    conn.close()
    flash('银行卡删除成功！')
    return redirect(url_for('admin_cards'))

# 存款业务类型管理
@app.route('/admin/deposits', methods=['GET', 'POST'])
def admin_deposits():
    conn = get_db_connection()
    query = "SELECT * FROM deposit WHERE 1=1"
    params = []
    savingID = request.args.get('savingID', '').strip()
    if savingID:
        query += " AND savingID LIKE %s"
        params.append(f"%{savingID}%")
    with conn.cursor() as cursor:
        cursor.execute(query, params)
        deposits = cursor.fetchall()
    conn.close()
    return render_template('admin/deposits.html', deposits=deposits, savingID=savingID)

@app.route('/admin/deposits/add', methods=['GET', 'POST'])
def admin_deposit_add():
    if request.method == 'POST':
        savingName = request.form['savingName']
        descrip = request.form['descrip']
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute("INSERT INTO deposit (savingName, descrip) VALUES (%s, %s)", (savingName, descrip))
            conn.commit()
        conn.close()
        flash('存款业务类型添加成功！')
        return redirect(url_for('admin_deposits'))
    return render_template('admin/deposit_add.html')

@app.route('/admin/deposits/edit/<int:savingID>', methods=['GET', 'POST'])
def admin_deposit_edit(savingID):
    conn = get_db_connection()
    if request.method == 'POST':
        savingName = request.form['savingName']
        descrip = request.form['descrip']
        with conn.cursor() as cursor:
            cursor.execute("UPDATE deposit SET savingName=%s, descrip=%s WHERE savingID=%s", (savingName, descrip, savingID))
            conn.commit()
        conn.close()
        flash('存款业务类型修改成功！')
        return redirect(url_for('admin_deposits'))
    else:
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM deposit WHERE savingID=%s", (savingID,))
            deposit = cursor.fetchone()
        conn.close()
        if not deposit:
            flash('未找到该类型！')
            return redirect(url_for('admin_deposits'))
        return render_template('admin/deposit_edit.html', deposit=deposit)

@app.route('/admin/deposits/delete/<int:savingID>', methods=['POST'])
def admin_deposit_delete(savingID):
    conn = get_db_connection()
    with conn.cursor() as cursor:
        cursor.execute("DELETE FROM deposit WHERE savingID=%s", (savingID,))
        conn.commit()
    conn.close()
    flash('存款业务类型删除成功！')
    return redirect(url_for('admin_deposits'))

# 客户模块
@app.route('/customer/login', methods=['GET', 'POST'])
def customer_login():
    if request.method == 'POST':
        cardID = request.form['cardID']
        password = request.form['password']
        conn = get_db_connection()
        with conn.cursor() as cursor:
            cursor.execute("SELECT c.*, u.customerName FROM cardinfo c JOIN userinfo u ON c.customerID = u.customerID WHERE cardID = %s", (cardID,))
            card = cursor.fetchone()
        conn.close()
        
        if card and card['pass'] == password:
            if card['IsReportLoss'] == '是':
                flash('该卡已挂失，请联系银行工作人员。')
                return redirect(url_for('customer_login'))
            session['cardID'] = cardID  #设置会话标识
            return redirect(url_for('customer_dashboard'))
        else:
            flash('卡号或密码错误')
            return redirect(url_for('customer_login'))
    return render_template('customer/login.html')


@app.route('/customer/logout')
def customer_logout():
    session.pop('cardID', None)  #清除会话中的卡号
    return redirect(url_for('customer_login'))

#获取信息
def get_card_info():
    if 'cardID' not in session:
        return None
    conn = get_db_connection()
    with conn.cursor() as cursor:
        cursor.execute("SELECT c.*, u.customerName FROM cardinfo c JOIN userinfo u ON c.customerID = u.customerID WHERE cardID = %s", (session['cardID'],))
        card = cursor.fetchone()
    conn.close()
    return card

#仪表盘
@app.route('/customer/dashboard')
def customer_dashboard():
    card_info = get_card_info()
    if not card_info:
        return redirect(url_for('customer_login'))
    return render_template('customer/dashboard.html', card_info=card_info)

#余额查询
@app.route('/customer/balance')
def customer_balance():
    card_info = get_card_info()
    if not card_info:
        return redirect(url_for('customer_login'))
    return render_template('customer/balance.html', card_info=card_info)

#交易记录查询
@app.route('/customer/trades')
def customer_trades():
    card_info = get_card_info()
    if not card_info:
        return redirect(url_for('customer_login'))
    
    trade_date = request.args.get('tradeDate', '')
    
    conn = get_db_connection()
    query = "SELECT * FROM tradeinfo WHERE cardID = %s"
    params = [card_info['cardID']]

    if trade_date:
        query += " AND tradeDate = %s"
        params.append(trade_date)
    
    query += " ORDER BY tradeDate DESC"  #按日期降序
    
    with conn.cursor() as cursor:
        cursor.execute(query, params)
        trades = cursor.fetchall()
    conn.close()
    
    return render_template('customer/trades.html',
                           card_info=card_info,
                           trades=trades,   #交易记录列表
                           tradeDate=trade_date)

#存款操作
@app.route('/customer/deposit', methods=['GET', 'POST'])
def customer_deposit():
    card_info = get_card_info()
    if not card_info:
        return redirect(url_for('customer_login'))
    
    if request.method == 'POST':
        amount = float(request.form['amount'])  #获取存款金额
        if amount <= 0:
            flash('存款金额必须大于0')
            return redirect(url_for('customer_deposit'))
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                # 更新余额
                cursor.execute("UPDATE cardinfo SET balance = balance + %s WHERE cardID = %s", (amount, card_info['cardID']))
                # 插入交易记录
            #    cursor.execute(
             #       """INSERT INTO tradeinfo
              #             (cardID, tradeType, tradeMoney, remark, tradeDate)
               #        VALUES (%s, %s, %s, %s, NOW())""",
                #    (card_info['cardID'], '存款', amount, '柜面存款')
                #)
                conn.commit()  #提交事务
            flash('存款成功！')
            return redirect(url_for('customer_dashboard'))
        except Exception as e:
            conn.rollback()  #回滚事务
            flash('存款失败，请稍后重试')
        finally:
            conn.close()
    
    return render_template('customer/deposit.html', card_info=card_info)

#取款操作
@app.route('/customer/withdraw', methods=['GET', 'POST'])
def customer_withdraw():
    card_info = get_card_info()
    if not card_info:
        return redirect(url_for('customer_login'))
    
    if request.method == 'POST':
        amount = float(request.form['amount'])
        if amount <= 0:
            flash('取款金额必须大于0')
            return redirect(url_for('customer_withdraw'))
        
        if amount > card_info['balance']:
            flash('余额不足')
            return redirect(url_for('customer_withdraw'))
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                # 更新余额
                cursor.execute("UPDATE cardinfo SET balance = balance - %s WHERE cardID = %s", (amount, card_info['cardID']))
                # 插入交易记录
             #   cursor.execute(
             #       """INSERT INTO tradeinfo
             #              (cardID, tradeType, tradeMoney, remark, tradeDate)
             #          VALUES (%s, %s, %s, %s, NOW())""",
             #       (card_info['cardID'], '取款', amount, '柜面取款')
             #   )
                conn.commit()
            flash('取款成功！')
            return redirect(url_for('customer_dashboard'))
        except Exception as e:
            conn.rollback()
            flash(f'取款失败: {str(e)}')  # 显示具体错误
        finally:
            conn.close()
    
    return render_template('customer/withdraw.html', card_info=card_info)

#挂失操作
@app.route('/customer/loss', methods=['GET', 'POST'])
def customer_loss():
    card_info = get_card_info()
    if not card_info:
        return redirect(url_for('customer_login'))
    
    if request.method == 'POST':
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("UPDATE cardinfo SET IsReportLoss = '是' WHERE cardID = %s", (card_info['cardID'],))
                conn.commit()
            flash('挂失成功！请联系银行工作人员处理后续事宜。')
            return redirect(url_for('customer_logout'))
        except Exception as e:
            conn.rollback()
            flash('挂失失败，请稍后重试')
        finally:
            conn.close()
    
    return render_template('customer/loss.html', card_info=card_info)

#修改密码
@app.route('/customer/password', methods=['GET', 'POST'])
def customer_password():
    card_info = get_card_info()
    if not card_info:
        return redirect(url_for('customer_login'))
    
    if request.method == 'POST':
        old_password = request.form['old_password']
        new_password = request.form['new_password']
        confirm_password = request.form['confirm_password']
        
        if old_password != card_info['pass']:
            flash('原密码错误')
            return redirect(url_for('customer_password'))
        
        if new_password != confirm_password:
            flash('两次输入的新密码不一致')
            return redirect(url_for('customer_password'))
        
        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                cursor.execute("UPDATE cardinfo SET pass = %s WHERE cardID = %s", (new_password, card_info['cardID']))
                conn.commit()
            flash('密码修改成功！请重新登录。')
            return redirect(url_for('customer_logout'))
        except Exception as e:
            conn.rollback()
            flash('密码修改失败，请稍后重试')
        finally:
            conn.close()
    
    return render_template('customer/password.html', card_info=card_info)

#转账操作
@app.route('/customer/transfer', methods=['GET', 'POST'])
def customer_transfer():
    card_info = get_card_info()
    if not card_info:
        return redirect(url_for('customer_login'))

    if request.method == 'POST':
        to_card = request.form['to_card']
        amount = float(request.form['amount'])

        conn = get_db_connection()
        try:
            with conn.cursor() as cursor:
                # 初始化会话变量
                cursor.execute("SET @is_transferring = FALSE")

                # 调用存储过程
                cursor.callproc('transfer_money',
                                [card_info['cardID'], to_card, amount, None, None])

                # 获取输出参数
                cursor.execute('SELECT @_transfer_money_3, @_transfer_money_4')
                result = cursor.fetchone()
                status, message = result['@_transfer_money_3'], result['@_transfer_money_4']

                if status:
                    flash(message)
                    return redirect(url_for('customer_dashboard'))
                else:
                    flash(message)
                    return redirect(url_for('customer_transfer'))
        except Exception as e:
            conn.rollback()
            flash(f'转账失败: {str(e)}')
        finally:
            # 清理会话变量
            try:
                with conn.cursor() as cursor:
                    cursor.execute("SET @is_transferring = NULL")
            except:
                pass
            conn.close()

    return render_template('customer/transfer.html', card_info=card_info)
if __name__ == '__main__':
    app.run(debug=True) 